﻿Imports System.Data.SqlClient

Public Class Engine
    ' Public UserTable As DataGridView
    Public Function Test() As Boolean
        Dim conn As New SqlClient.SqlConnection(My.Settings.SqlConnectionString)
        Try
            conn.Open()
            conn.Close()
            Return True
        Catch ex As Exception
            conn.Close()
            MsgBox(ex.Message)
            Return False
        End Try
    End Function

    Function UserDataSource(AccountType As String) As Object
        Dim constring As String = My.Settings.SqlConnectionString
        Using con As New SqlConnection(constring)
            Using cmd As New SqlCommand("SELECT TOP 256 [username] as [BorrowersID], " _
                                        & " lastname as [Last Name]," _
                                        & " firstname as [First Name]," _
                                        & " middlename as [Middle Name]," _
                                        & " contactnumber as [Contact No.]," _
                                        & " section as [Section]," _
                                        & " yearlevel as [Year-Level]" _
                                        & " FROM " & My.Settings.UsersTable & " WHERE [accounttype] = @accounttype", con)
                'MsgBox(cmd.CommandText)
                cmd.Parameters.AddWithValue("@accounttype", AccountType)
                cmd.CommandType = CommandType.Text
                Using sda As New SqlDataAdapter(cmd)
                    Using dt As New DataTable()
                        sda.Fill(dt)
                        'dataGridView1.DataSource = dt
                        Return dt
                    End Using
                End Using
            End Using
        End Using
    End Function

End Class
Public Class Borrower
    Public ReturnString As String
    Public ReturnB As Boolean
    Public username, lastname, firstname, middlename, section, yearlevel, address, contactnumber As String
    Sub Add()
        Dim constring As String = My.Settings.SqlConnectionString
        Try
            Using con As New SqlConnection(constring)
                Dim cmd As New SqlCommand
                cmd.Connection = con
                cmd.CommandText = "INSERT INTO " & My.Settings.UsersTable & " ([username], [lastname], [firstname], [middlename], [section], [yearlevel], [address], [contactnumber], [borrowinstatus], [accounttype]) " _
                                                                   & " values (@username, @lastname, @firstname, @middlename, @section, @yearlevel, @address, @contactnumber, @borrowinstatus, @accounttype)"
                con.Open()
                cmd.Parameters.AddWithValue("@username", username)
                cmd.Parameters.AddWithValue("@lastname", lastname)
                cmd.Parameters.AddWithValue("@firstname", firstname)
                cmd.Parameters.AddWithValue("@middlename", middlename)
                cmd.Parameters.AddWithValue("@section", section)
                cmd.Parameters.AddWithValue("@yearlevel", yearlevel)
                cmd.Parameters.AddWithValue("@address", address)
                cmd.Parameters.AddWithValue("@contactnumber", contactnumber)
                cmd.Parameters.AddWithValue("@borrowinstatus", "ACTIVE")
                cmd.Parameters.AddWithValue("@accounttype", "BORROWER")
                cmd.ExecuteNonQuery()
                con.Close()
            End Using
            ReturnB = True
            ReturnString = "Success"
        Catch ex As Exception
            ReturnB = False
            ReturnString = ex.Message
        End Try
    End Sub
End Class